package Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import marvin.model.MemberVO;
import marvin.utils.OracleJDBC;
import marvin.utils.OracleJDBC_1;

public class Test1_1DAO {
	
	private static final String insertSQL = 
			"INSERT INTO TABLE2 (USER_ID,USER_NAME,USER_AGE,USER_SEX,USER_TEL,USER_PW,USER_EMAIL) VALUES(?,?,?,?,?,?,?) ";
	private final String updateSQL ="UPDATE TABLE2 SET USER_AGE = ?,USER_TEL = ?,USER_PW = ?, UPDATE = SYSDATE WHERE USER_ID =?";
	private final String deleteSQL ="DELETE FROM TABLE2 WHERE USER_ID = ?";
	private final String selectSQL ="SELECT USER_ID,USER_NAME FROM TABLE2 ORDER BY USER_ID DESC";
	private final String selectOneSQL ="SELECT USER_ID,USER_NAME,USER_TEL,USER_PW,USER_EMAIL WHERE USER_ID = ?";
	private final String selectUidSQL = "select user_id from table2 where user_id = ?";

	public int insertMember(Test1_1VO t){
		Connection conn = null;
		PreparedStatement ps = null;
		
		int cnt = 0;
		
		try {
			conn = OracleJDBC_1.openConn();
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, t.getUid());
				ps.setString(2, t.getName());
				ps.setString(3, t.getAge());
				ps.setString(4, t.getGender());
				ps.setString(5, t.getPhone());
				ps.setString(6, t.getPwd());
				ps.setString(7, t.getMail());
			cnt = ps.executeUpdate();
		}catch( Exception ex){
		}finally{
			OracleJDBC_1.closeConn(conn, ps, null);
		}
		return cnt;
		
	}
	
	public int updateMember(Test1_1VO t){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC_1.openConn();
		}catch(Exception ex){
		}finally{
			OracleJDBC_1.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	public int deleteMember(String mno){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC_1.openConn();
		}catch(Exception ex){
		}finally{
			OracleJDBC_1.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	// 회원 전체조회 select
	public ArrayList<Test1_1VO> selectMember(){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Test1_1VO> list
						= new ArrayList<>();
		
		try{
			conn = OracleJDBC_1.openConn();
			while(rs.next()){
				Test1_1VO t = new Test1_1VO(rs.getString(2),rs.getString(3),rs.getString(4),"","","","");
				t.setUid(rs.getString(1));
				
				list.add(t);
			}
			}catch(Exception ex){
				ex.printStackTrace();
			
			}finally{
				OracleJDBC_1.closeConn(conn, ps, rs);
		}
	
		return list;
	}
	
	// 회원 상세조회 selectOne
	public Test1_1VO selectOneMember(String uid){
		Test1_1VO t = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
//		try{
//			conn = OracleJDBC_1.openConn();
//				ps.setString(1, uid);
//			while(rs.next()){
//				t = new Test1_1VO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7));
//			}
//		}
//		
//		
		return t;
	}
	
	public Test1_1VO selectUserid(String uid){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		Test1_1VO t = null;
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectUidSQL);
				ps.setString(1, uid);
			rs = ps.executeQuery();
			
			while(rs.next()){
				 t = new Test1_1VO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7));
				
				}
			}catch(Exception ex){													
				ex.printStackTrace();
			}finally{
				OracleJDBC.closeConn(conn, ps, rs);}
		return t;
	}
	
	
	}	

	
	
	
	
	
	
	
	

